﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Text;

/// <summary>
/// 文件名：ClsUserLoginDAL.cs
/// 作  者：黄纲
/// 时  间：2006.11.1
/// 说  明：ClsUserLoginDAL类用于用户登录相关操作
/// </summary>
public class ClsUserLoginDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    #region 系统用户管理

    /// <summary>
    /// 获取系统用户类别
    /// </summary>
    /// <param name="UserID">用户登录ID</param>
    /// <returns></returns>
    public DataSet GetSysUserType(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysUserType", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取系统用户信息
    /// </summary>
    /// <param name="UserID">用户登录ID</param>
    /// <returns></returns>
    public DataSet GetSysUsers(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysUsers", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst; 
    }

    /// <summary>
    /// 系统用户是否存在
    /// </summary>
    /// <param name="UserID">用户登录ID</param>
    /// <returns></returns>
    public int SysUserIDIsExist(string UserLoginID)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SysUserIDIsExist", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        com.Parameters["@u_log_id"].Value = UserLoginID;

        PrmOutPut = com.Parameters.Add("@retCount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return (int)com.Parameters["@retCount"].Value;
        }
        catch
        {
            return -3;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 用户登录
    /// </summary>
    /// <param name="UserLoginID">用户登录ID</param>
    /// <param name="UserLoginPwd">登录密码</param>
    /// <param name="C_Name">登录计算机名</param>
    /// <param name="C_Ip">登录IP</param>
    /// <returns></returns>
    public int SysUserLogin(string UserLoginID, string UserLoginPwd, string C_Name, string C_Ip, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SysUserLogin", con);
        SqlParameter outPut = new SqlParameter();
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        com.Parameters["@u_log_id"].Value = UserLoginID;
        com.Parameters.Add("@u_pwd", SqlDbType.NVarChar, 255);
        com.Parameters["@u_pwd"].Value = Md5(UserLoginPwd);
        com.Parameters.Add("@c_name", SqlDbType.NVarChar, 50);
        com.Parameters["@c_name"].Value = C_Name;
        com.Parameters.Add("@c_ip", SqlDbType.NVarChar, 50);
        com.Parameters["@c_ip"].Value = C_Ip;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        outPut = com.Parameters.Add("@msgID", SqlDbType.Int, 4);
        outPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return (int)com.Parameters["@msgID"].Value;
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 添加系统用户
    /// </summary>
    /// <param name="UserNo">用户编号</param>
    /// <param name="WorkID">用户名称</param>
    /// <param name="UserLoginID">用户登录ID</param>
    /// <param name="UserPwd">用户登录密码</param>
    /// <param name="PwdOverDate">密码有效期</param>
    /// <param name="UserStopState">停用否(0否，1是)</param>
    /// <param name="UserType">用户所属角色ID</param>
    /// <param name="UserStation">操作员岗位</param>
    /// <param name="BeginDate">用户启用时间</param>
    /// <param name="UserCreateor">用户创建者</param>
    /// <returns>添加是否成功</returns>
    public Boolean AddSysUser(string UserNo, string WorkID, string UserLoginID, string UserPwd, int PwdOverDate, int UserStopState, int UserType, string UserStation, DateTime BeginDate, string UserCreateor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddSysUser", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_no", SqlDbType.NVarChar, 60);
        com.Parameters["@u_no"].Value = UserNo;
        com.Parameters.Add("@u_wk_id", SqlDbType.NVarChar, 60);
        com.Parameters["@u_wk_id"].Value = WorkID;
        com.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        com.Parameters["@u_log_id"].Value = UserLoginID;
        com.Parameters.Add("@u_pwd", SqlDbType.NVarChar, 255);
        com.Parameters["@u_pwd"].Value = Md5(UserPwd);
        com.Parameters.Add("@u_pwd_over_date", SqlDbType.Int, 4);
        com.Parameters["@u_pwd_over_date"].Value = PwdOverDate;
        com.Parameters.Add("@u_is_stop", SqlDbType.Bit);
        com.Parameters["@u_is_stop"].Value = UserStopState;
        com.Parameters.Add("@u_type", SqlDbType.Int, 4);
        com.Parameters["@u_type"].Value = UserType;
        com.Parameters.Add("@u_station", SqlDbType.NVarChar, 60);
        com.Parameters["@u_station"].Value = UserStation;
        com.Parameters.Add("@u_beg_date", SqlDbType.DateTime, 8);
        com.Parameters["@u_beg_date"].Value = BeginDate;
        com.Parameters.Add("@u_createor", SqlDbType.NVarChar, 60);
        com.Parameters["@u_createor"].Value = UserCreateor;

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改系统用户状态
    /// </summary>
    /// <param name="ID">ID号</param>
    /// <param name="PwdOverDate">密码有效期</param>
    /// <param name="UserStopState">停用状态</param>
    /// <param name="UserType">操作员性质</param>
    /// <param name="UserStation">操作员岗位</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateUsetState(int ID, int PwdOverDate, int UserStopState, int UserType, string UserStation)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateUserState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = ID;
        com.Parameters.Add("@u_pwd_over_date", SqlDbType.Int, 4);
        com.Parameters["@u_pwd_over_date"].Value = PwdOverDate;
        com.Parameters.Add("@u_is_stop", SqlDbType.Bit);
        com.Parameters["@u_is_stop"].Value = UserStopState;
        com.Parameters.Add("@u_type", SqlDbType.Int, 4);
        com.Parameters["@u_type"].Value = UserType;
        com.Parameters.Add("@u_station", SqlDbType.NVarChar, 60);
        com.Parameters["@u_station"].Value = UserStation;

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改用户密码
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="NewPwd">用户新密码</param>
    /// <returns></returns>
    public Boolean UpdateUserPwd(int UserID, string NewPwd)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateUserPwd", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@u_log_pwd", SqlDbType.NVarChar, 255);
        com.Parameters["@u_log_pwd"].Value = Md5(NewPwd);

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改用户密码
    /// </summary>
    /// <param name="UserID">用户登录ID</param>
    /// <param name="NewPwd">用户新密码</param>
    /// <returns></returns>
    public Boolean UpdateUserPwd(string UserID, string NewPwd)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateUserPwdByOldPwd", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        com.Parameters["@u_log_id"].Value = UserID;
        com.Parameters.Add("@u_log_pwd", SqlDbType.NVarChar, 255);
        com.Parameters["@u_log_pwd"].Value = Md5(NewPwd);

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废系统用户
    /// </summary>
    /// <param name="ID">用户ID</param>
    /// <param name="Type">(0作废，1彻底作废)</param>
    /// <returns></returns>
    public Boolean DeleteSysUser(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteSysUser", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// MD5加密
    /// </summary>
    /// <param name="Password">未加密的密码</param>
    /// <returns>加密后的密码</returns>
    private static String Md5(String Password)
    {
        Byte[] clearBytes = new UnicodeEncoding().GetBytes(Password);

        Byte[] hashedBytes = ((HashAlgorithm)CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);
        //返回加密后的密码
        return BitConverter.ToString(hashedBytes);
    }

    #endregion 

    #region 用户登陆管理

    /// <summary>
    /// 系统用户退出系统
    /// </summary>
    /// <param name="UserID"></param>
    /// <param name="C_Name"></param>
    /// <param name="C_Ip"></param>
    /// <returns></returns>
    public Boolean OutLogin(string UserID, string C_Name, string C_Ip)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OutLogin", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 50);
        com.Parameters["@u_log_id"].Value = UserID;
        com.Parameters.Add("@c_name", SqlDbType.NVarChar, 50);
        com.Parameters["@c_name"].Value = C_Name;
        com.Parameters.Add("@c_ip", SqlDbType.NVarChar, 50);
        com.Parameters["@c_ip"].Value = C_Ip;

        try
        {
            con.Open();

            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取系统用户登陆信息
    /// </summary>
    /// <returns></returns>
    public DataSet GetUserLoginDataSet()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetUserLoginDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    #endregion
}
